package com.nce.service;

import java.math.BigInteger;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.Query;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;
import org.springside.modules.orm.Page;
import org.springside.modules.orm.PropertyFilter;

import com.nce.dao.MsgDao;
import com.nce.entity.Msg;
import com.nce.entity.MsgStats;
import com.nce.entity.Vp;

@Component
@Transactional
public class MsgService {
	@Autowired
	private MsgDao msgDao;
	
	private static Logger logger = LoggerFactory.getLogger(MsgService.class);

	public void saveMsg(Msg msg) {
		msgDao.save(msg);
		
	}

	public int saveMsgList(List<Msg> msgList) {
		int failCnt = 0;
		for(Msg msg : msgList){
			try{
				msgDao.save(msg);
			}catch(Exception e){
				failCnt++;
				logger.error("insert error the msg is : "+msg,e);
				e.printStackTrace();
				throw new ServiceException(e.getMessage()+msg.toString(),e);
			}
		}		
		return failCnt;
	}
	
	public Page<MsgStats> findQQMsgStatsPage(String sdateStr, String edateStr, String minwordsStr, String maxwordsStr, String group, Page<MsgStats> page) {
		List<MsgStats> msgList = new ArrayList<MsgStats>();
		String preSql = "select m.qqid, count(m.qqid) counts,m.username,m.qqgroup from Msg m";		
		String whereSql = " where 1 = 1";
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwordsStr)){
			whereSql += (" and word_counts >= "+minwordsStr);
		}
		
		if(StringUtils.isNotEmpty(maxwordsStr)){
			whereSql += (" and word_counts <= "+maxwordsStr);
		}
		if(StringUtils.isNotEmpty(group)){
			whereSql += (" and qqgroup = '"+group+"' ");
		}
		String postSql = " group by m.qqid order by counts desc ";
	
		
		String sql = preSql+whereSql+postSql;
		BigInteger counts = (BigInteger)msgDao.getSession().createSQLQuery("select count(*) from ("+sql+" ) tmp").uniqueResult();
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.setFirstResult(page.getFirst() - 1).setMaxResults(page.getPageSize()).list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   MsgStats msgStats = new MsgStats();
			   msgStats.setQqid((String)arr[0]);
			   msgStats.setCounts(((BigInteger)arr[1]).longValue());
			   msgStats.setUsername((String)arr[2]);
			   msgStats.setQqgroup((String)arr[3]);
			   msgList.add(msgStats);
		        //System.out.println(msgStats.toString());
		   }

		   page.setTotalCount(counts.longValue());
		   page.setResult(msgList);

		return page;
	}
	
	public Page<MsgStats> findQQMsgStatsPageWithVp(String sdateStr, String edateStr, String minwordsStr, String maxwordsStr, String group, Page<MsgStats> page) {
		List<MsgStats> msgList = new ArrayList<MsgStats>();
		String preSql = "select m.qqid, count(m.qqid) counts,m.username,m.qqgroup ,v.realname,v.address,v.id from Msg m,Vp v ";		
		String whereSql = " where 1 = 1 and m.qqid = v.qqid ";
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and m.ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and m.ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwordsStr)){
			whereSql += (" and m.word_counts >= "+minwordsStr);
		}
		
		if(StringUtils.isNotEmpty(maxwordsStr)){
			whereSql += (" and m.word_counts <= "+maxwordsStr);
		}
		if(StringUtils.isNotEmpty(group)){
			whereSql += (" and m.qqgroup = '"+group+"' ");
		}
		String postSql = " group by m.qqid order by counts desc ";
	
		
		String sql = preSql+whereSql+postSql;
		BigInteger counts = (BigInteger)msgDao.getSession().createSQLQuery("select count(*) from ("+sql+" ) tmp").uniqueResult();
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.setFirstResult(page.getFirst() - 1).setMaxResults(page.getPageSize()).list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   MsgStats msgStats = new MsgStats();
			   msgStats.setQqid((String)arr[0]);
			   msgStats.setCounts(((BigInteger)arr[1]).longValue());
			   msgStats.setUsername((String)arr[2]);
			   msgStats.setQqgroup((String)arr[3]);
			   Vp vp = new Vp();
			   vp.setUsername((String)arr[4]);
			   vp.setAddress((String)arr[5]);
			   vp.setId(((BigInteger)arr[6]).longValue());
			   msgStats.setVp(vp);			
			   msgList.add(msgStats);
		        System.out.println(msgStats.toString());
		   }

		   page.setTotalCount(counts.longValue());
		   page.setResult(msgList);

		return page;
	}
	
	public Page<MsgStats> findQQMsgStatsPageWithoutVp(String sdateStr, String edateStr, String minwordsStr, String maxwordsStr, String group, Page<MsgStats> page) {
		List<MsgStats> msgList = new ArrayList<MsgStats>();
		String preSql = "select m.qqid, count(m.qqid) counts,m.username,m.qqgroup from Msg m ,Vp v";		
		String whereSql = "  where 1 = 1 and m.qqid != v.qqid";
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and m.ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and m.ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwordsStr)){
			whereSql += (" and m.word_counts >= "+minwordsStr);
		}
		
		if(StringUtils.isNotEmpty(maxwordsStr)){
			whereSql += (" and m.word_counts <= "+maxwordsStr);
		}
		if(StringUtils.isNotEmpty(group)){
			whereSql += (" and m.qqgroup = '"+group+"' ");
		}
		String postSql = " group by m.qqid order by counts desc ";
	
		
		String sql = preSql+whereSql+postSql;
		BigInteger counts = (BigInteger)msgDao.getSession().createSQLQuery("select count(*) from ("+sql+" ) tmp").uniqueResult();
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.setFirstResult(page.getFirst() - 1).setMaxResults(page.getPageSize()).list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   MsgStats msgStats = new MsgStats();
			   msgStats.setQqid((String)arr[0]);
			   msgStats.setCounts(((BigInteger)arr[1]).longValue());
			   msgStats.setUsername((String)arr[2]);
			   msgStats.setQqgroup((String)arr[3]);			
			   msgList.add(msgStats);
		        //System.out.println(msgStats.toString());
		   }

		   page.setTotalCount(counts.longValue());
		   page.setResult(msgList);

		return page;
	}

	public List<MsgStats> findQQMsgStatsList(String sdateStr, String edateStr,
			String minwordsStr, String maxwordsStr, String group) {
		List<MsgStats> msgList = new ArrayList<MsgStats>();
		String preSql = "select m.qqid, count(m.qqid) counts,m.username,m.qqgroup from Msg m ";		
		String whereSql = " where 1 = 1";
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwordsStr)){
			whereSql += (" and word_counts >= "+minwordsStr);
		}
		
		if(StringUtils.isNotEmpty(maxwordsStr)){
			whereSql += (" and word_counts <= "+maxwordsStr);
		}
		if(StringUtils.isNotEmpty(group)){
			whereSql += (" and qqgroup = '"+group+"' ");
		}
		String postSql = " group by m.qqid order by counts desc ";
	
		
		String sql = preSql+whereSql+postSql;		
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   MsgStats msgStats = new MsgStats();
			   msgStats.setQqid((String)arr[0]);
			   msgStats.setCounts(((BigInteger)arr[1]).longValue());
			   msgStats.setUsername((String)arr[2]);
			   msgStats.setQqgroup((String)arr[3]);
			   msgList.add(msgStats);
		        //System.out.println(msgStats.toString());
		   }

		return msgList;
	}

	public List<MsgStats> findQQMsgStatsListWithVp(String sdateStr, String edateStr,
			String minwordsStr, String maxwordsStr, String group) {
		List<MsgStats> msgList = new ArrayList<MsgStats>();
		String preSql = "select m.qqid, count(m.qqid) counts,m.username,m.qqgroup ,v.realname,v.address,v.id from Msg m,Vp v ";		
		String whereSql = " where 1 = 1 and m.qqid = v.qqid ";
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and m.ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and m.ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwordsStr)){
			whereSql += (" and m.word_counts >= "+minwordsStr);
		}
		
		if(StringUtils.isNotEmpty(maxwordsStr)){
			whereSql += (" and m.word_counts <= "+maxwordsStr);
		}
		if(StringUtils.isNotEmpty(group)){
			whereSql += (" and m.qqgroup = '"+group+"' ");
		}
		String postSql = " group by m.qqid order by counts desc ";
	
		
		String sql = preSql+whereSql+postSql;
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   MsgStats msgStats = new MsgStats();
			   msgStats.setQqid((String)arr[0]);
			   msgStats.setCounts(((BigInteger)arr[1]).longValue());
			   msgStats.setUsername((String)arr[2]);
			   msgStats.setQqgroup((String)arr[3]);
			   
			   Vp vp = new Vp();
			   vp.setUsername((String)arr[4]);
			   vp.setAddress((String)arr[5]);
			   vp.setId(((BigInteger)arr[6]).longValue());
			   msgStats.setVp(vp);			
			   msgList.add(msgStats);
		        //System.out.println(msgStats.toString());
		   }
		return msgList;
	}

	public List<MsgStats> findQQMsgStatsListWithoutVp(String sdateStr,
			String edateStr, String minwordsStr, String maxwordsStr, String group) {
		List<MsgStats> msgList = new ArrayList<MsgStats>();
		String preSql = "select m.qqid, count(m.qqid) counts,m.username,m.qqgroup from Msg m,Vp v ";		
		String whereSql = " where 1 = 1 and m.qqid != v.qqid ";
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and m.ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and m.ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwordsStr)){
			whereSql += (" and m.word_counts >= "+minwordsStr);
		}
		
		if(StringUtils.isNotEmpty(maxwordsStr)){
			whereSql += (" and m.word_counts <= "+maxwordsStr);
		}
		if(StringUtils.isNotEmpty(group)){
			whereSql += (" and m.qqgroup = '"+group+"' ");
		}
		String postSql = " group by m.qqid order by counts desc ";
	
		
		String sql = preSql+whereSql+postSql;
		BigInteger counts = (BigInteger)msgDao.getSession().createSQLQuery("select count(*) from ("+sql+" ) tmp").uniqueResult();
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   MsgStats msgStats = new MsgStats();
			   msgStats.setQqid((String)arr[0]);
			   msgStats.setCounts(((BigInteger)arr[1]).longValue());
			   msgStats.setUsername((String)arr[2]);
			   msgStats.setQqgroup((String)arr[3]);
			   msgList.add(msgStats);
		        //System.out.println(msgStats.toString());
		   }

		return msgList;
	}

	public Page<Msg> findPageMsg(List<PropertyFilter> filters, Page<Msg> page) {
		return msgDao.findPage(page, filters);
	}
	
	public Page<Msg> findPageMsgWithVp(String qqid,String sdateStr,String edateStr,String minwordsStr,String maxwordsStr,String group,String username,String contentExp, Page<Msg> page,boolean isOr) {
		List<Msg> msgList = new ArrayList<Msg>();
		String preSql = "select m.qqid,m.ctime,m.username,m.qqgroup,m.content,m.word_counts,v.realname,v.address ,v.id from Msg m,Vp v ";		
		String whereSql = " where 1 = 1 and m.qqid = v.qqid ";
		if(StringUtils.isNotEmpty(qqid)){
			whereSql += (" and m.qqid = '"+qqid+"' ");
		}
		
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and m.ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and m.ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwordsStr)){
			whereSql += (" and m.word_counts >= "+minwordsStr);
		}
		
		if(StringUtils.isNotEmpty(maxwordsStr)){
			whereSql += (" and m.word_counts <= "+maxwordsStr);
		}
		if(StringUtils.isNotEmpty(group)){
			whereSql += (" and m.qqgroup = '"+group+"' ");
		}
		
		if(StringUtils.isNotEmpty(username)){
			whereSql += (" and m.username regexp '"+username+"' ");
		}
		
//		if(StringUtils.isNotEmpty(contentExp)){
//		whereSql += (" and m.content regexp '"+contentExp+"' ");
//	}
	
		//搜索多个词用空格分开
		if (StringUtils.isNotEmpty(contentExp)) {
			String [] words = contentExp.split(" ");
			for(int i = 0; i < words.length; i++ ){
				if(StringUtils.isNotEmpty(words[i])){
					if(StringUtils.isNotEmpty(contentExp)){
						if(isOr){
							if(i == 0){
								whereSql += (" and (m.content regexp '"+words[i]+"' ");
							}else if(i == (words.length -1)){
								whereSql += (" or m.content regexp '"+words[i]+"' )");
							}else{
								whereSql += (" or m.content regexp '"+words[i]+"' ");
							}
							
						}else{
							whereSql += (" and m.content regexp '"+words[i]+"' ");
						}
					}
				}
			}
		}
		
		String postSql = " order by qqid,ctime desc ";
	
		
		String sql = preSql+whereSql+postSql;
		BigInteger counts = (BigInteger)msgDao.getSession().createSQLQuery("select count(*) from ("+sql+" ) tmp").uniqueResult();
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.setFirstResult(page.getFirst() - 1).setMaxResults(page.getPageSize()).list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   Msg msg = new Msg();
			   msg.setQqid((String)arr[0]);		
			   if(arr[1]!=null){
				   msg.setCtime((Date)arr[1]);
			   }
			   msg.setUsername((String)arr[2]);
			   msg.setQqgroup((String)arr[3]);
			   msg.setContent((String)arr[4]);
			   msg.setWordCounts((Integer)arr[5]);
			   
			   Vp vp = new Vp();
			   vp.setUsername((String)arr[6]);
			   vp.setAddress((String)arr[7]);
			   vp.setId(((BigInteger)arr[8]).longValue());
			   msg.setVp(vp);		
			   
			   msgList.add(msg);
		   }

		   page.setTotalCount(counts.longValue());
		   page.setResult(msgList);

		return page;
	}
	
	public Page<Msg> findPageMsgWithoutVp(String qqid,String sdateStr,String edateStr,String minwordsStr,String maxwordsStr,String group,String username,String contentExp, Page<Msg> page,boolean isOr) {
		List<Msg> msgList = new ArrayList<Msg>();
		String preSql = "select m.qqid,m.ctime,m.username,m.qqgroup,m.content,m.word_counts from Msg m ";		
		String whereSql = " where 1 = 1 ";
		if(StringUtils.isNotEmpty(qqid)){
			whereSql += (" and m.qqid = '"+qqid+"' ");
		}
		
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and m.ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and m.ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwordsStr)){
			whereSql += (" and m.word_counts >= "+minwordsStr);
		}
		
		if(StringUtils.isNotEmpty(maxwordsStr)){
			whereSql += (" and m.word_counts <= "+maxwordsStr);
		}
		if(StringUtils.isNotEmpty(group)){
			whereSql += (" and m.qqgroup = '"+group+"' ");
		}
		
		if(StringUtils.isNotEmpty(username)){
			whereSql += (" and m.username regexp '"+username+"' ");
		}
		
//		if(StringUtils.isNotEmpty(contentExp)){
//		whereSql += (" and m.content regexp '"+contentExp+"' ");
//	}
	

	
	//搜索多个词用空格分开
	if (StringUtils.isNotEmpty(contentExp)) {
		String [] words = contentExp.split(" ");
		for(int i = 0; i < words.length; i++ ){
			if(StringUtils.isNotEmpty(words[i])){
				if(StringUtils.isNotEmpty(contentExp)){
					if(isOr){
						if(i == 0){
							whereSql += (" and (m.content regexp '"+words[i]+"' ");
						}else if(i == (words.length -1)){
							whereSql += (" or m.content regexp '"+words[i]+"' )");
						}else{
							whereSql += (" or m.content regexp '"+words[i]+"' ");
						}
						
					}else{
						whereSql += (" and m.content regexp '"+words[i]+"' ");
					}
				}
			}
		}
	}
		
		String postSql = " order by qqid,ctime desc ";
	
		
		String sql = preSql+whereSql+postSql;
		BigInteger counts = (BigInteger)msgDao.getSession().createSQLQuery("select count(*) from ("+sql+" ) tmp").uniqueResult();
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.setFirstResult(page.getFirst() - 1).setMaxResults(page.getPageSize()).list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   Msg msg = new Msg();
			   msg.setQqid((String)arr[0]);		
			   if(arr[1]!=null){
				   msg.setCtime((Date)arr[1]);
			   }
			   msg.setUsername((String)arr[2]);
			   msg.setQqgroup((String)arr[3]);
			   msg.setContent((String)arr[4]);
			   msg.setWordCounts((Integer)arr[5]);
			   
			   msgList.add(msg);
		   }

		   page.setTotalCount(counts.longValue());
		   page.setResult(msgList);

		return page;
	}
	
	/**
	 * 或操作查询
	 * @param qqid
	 * @param sdateStr
	 * @param edateStr
	 * @param minwordsStr
	 * @param maxwordsStr
	 * @param group
	 * @param username
	 * @param contentExp
	 * @return
	 */
	public List<Msg> findList1(String qqid ,String sdateStr,String edateStr,String minwordsStr,String maxwordsStr,String group,String username,String contentExp) {
		List<Msg> msgList = new ArrayList<Msg>();
		String preSql = "select m.qqid,m.ctime,m.username,m.qqgroup,m.content,m.word_counts,v.realname,v.address,v.id from Msg m,Vp v ";		
		String whereSql = " where 1 = 1 and m.qqid = v.qqid ";
		if(StringUtils.isNotEmpty(qqid)){
			whereSql += (" and m.qqid = '"+qqid+"' ");
		}
		
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and m.ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and m.ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwordsStr)){
			whereSql += (" and m.word_counts >= "+minwordsStr);
		}
		
		if(StringUtils.isNotEmpty(maxwordsStr)){
			whereSql += (" and m.word_counts <= "+maxwordsStr);
		}
		if(StringUtils.isNotEmpty(group)){
			whereSql += (" and m.qqgroup = '"+group+"' ");
		}
		
		if(StringUtils.isNotEmpty(username)){
			whereSql += (" and m.username regexp '"+username+"' ");
		}
		
//		if(StringUtils.isNotEmpty(contentExp)){
//			whereSql += (" and m.content regexp '"+contentExp+"' ");
//		}
		
		//搜索多个词用空格分开
		if (StringUtils.isNotEmpty(contentExp)) {
			String [] words = contentExp.split(" ");
			for(String word :words){
				if(StringUtils.isNotEmpty(word)){
					if(StringUtils.isNotEmpty(contentExp)){
						whereSql += (" and m.content regexp '"+word+"' ");
					}
				}
			}
		}
		
		String postSql = " order by qqid,ctime desc ";
	
		
		String sql = preSql+whereSql+postSql;
		BigInteger counts = (BigInteger)msgDao.getSession().createSQLQuery("select count(*) from ("+sql+" ) tmp").uniqueResult();
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   Msg msg = new Msg();
			   msg.setQqid((String)arr[0]);		
			   if(arr[1]!=null){
				   DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			       Date date = null;
				try {
					date = (Date)formatter.parse((String)arr[1]);
				} catch (ParseException e) {
					e.printStackTrace();
				}
				   msg.setCtime(date);
			   }
			   msg.setUsername((String)arr[2]);
			   msg.setQqgroup((String)arr[3]);
			   msg.setContent((String)arr[4]);
			   msg.setWordCounts((Integer)arr[5]);
			   Vp vp = new Vp();
			   vp.setUsername((String)arr[6]);
			   vp.setAddress((String)arr[7]);
			   vp.setId(((BigInteger)arr[8]).longValue());
			   msg.setVp(vp);	
			   msgList.add(msg);
		   }

		return msgList;
	}

	public void dleteAllMsg() {
		
		msgDao.getSession().createSQLQuery("TRUNCATE msg").executeUpdate();		
	}
	
	public void deleteMsgByDate(String sdate, String edate) {
		msgDao.getSession().createSQLQuery("delete from msg where ctime > '"+sdate+ "'and ctime < '"+edate+"'").executeUpdate();		
		
	}

	public List<Msg> findMsgList(List<PropertyFilter> filters) {
		
		return msgDao.find(filters);
	}

	public List<Msg> findMsgListWithVp(String qqid, String sdateStr, String edateStr, String minwords, String maxwords,
			String qqgroup, String username, String content, Page<Msg> page,boolean isOr) {
		List<Msg> msgList = new ArrayList<Msg>();
		String preSql = "select m.qqid,m.ctime,m.username,m.qqgroup,m.content,m.word_counts,v.realname,v.address ,v.id from Msg m,Vp v ";		
		String whereSql = " where 1 = 1 and m.qqid = v.qqid ";
		if(StringUtils.isNotEmpty(qqid)){
			whereSql += (" and m.qqid = '"+qqid+"' ");
		}
		
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and m.ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and m.ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwords)){
			whereSql += (" and m.word_counts >= "+minwords);
		}
		
		if(StringUtils.isNotEmpty(maxwords)){
			whereSql += (" and m.word_counts <= "+maxwords);
		}
		if(StringUtils.isNotEmpty(qqgroup)){
			whereSql += (" and m.qqgroup = '"+qqgroup+"' ");
		}
		
		if(StringUtils.isNotEmpty(username)){
			whereSql += (" and m.username regexp '"+username+"' ");
		}
		
//		if(StringUtils.isNotEmpty(contentExp)){
//		whereSql += (" and m.content regexp '"+contentExp+"' ");
//	}
	
	//搜索多个词用空格分开
	if (StringUtils.isNotEmpty(content)) {
		String [] words = content.split(" ");
		for(String word :words){
			if(StringUtils.isNotEmpty(word)){
				if(StringUtils.isNotEmpty(content)){
					if(isOr){
						whereSql += (" or m.content regexp '"+word+"' ");
					}else{
						whereSql += (" and m.content regexp '"+word+"' ");
					}
				}
			}
		}
	}
		
		String postSql = " order by m.qqid,m.ctime desc ";
	
		
		String sql = preSql+whereSql+postSql;
		BigInteger counts = (BigInteger)msgDao.getSession().createSQLQuery("select count(*) from ("+sql+" ) tmp").uniqueResult();
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   Msg msg = new Msg();
			   msg.setQqid((String)arr[0]);		
			   if(arr[1]!=null){
				   msg.setCtime((Date)arr[1]);
			   }
			   msg.setUsername((String)arr[2]);
			   msg.setQqgroup((String)arr[3]);
			   msg.setContent((String)arr[4]);
			   msg.setWordCounts((Integer)arr[5]);
			   
			   Vp vp = new Vp();
			   vp.setUsername((String)arr[6]);
			   vp.setAddress((String)arr[7]);
			   vp.setId(((BigInteger)arr[8]).longValue());
			   msg.setVp(vp);		
			   
			   msgList.add(msg);
		   }

		return msgList;
	}
	
	public List<Msg> findMsgListWithoutVp(String qqid, String sdateStr, String edateStr, String minwords, String maxwords,
			String qqgroup, String username, String content, Page<Msg> page,boolean isOr) {
		List<Msg> msgList = new ArrayList<Msg>();
		String preSql = "select m.qqid,m.ctime,m.username,m.qqgroup,m.content,m.word_counts from Msg m ";		
		String whereSql = " where 1 = 1  ";
		if(StringUtils.isNotEmpty(qqid)){
			whereSql += (" and m.qqid = '"+qqid+"' ");
		}
		
		if(StringUtils.isNotEmpty(sdateStr)){
			whereSql += (" and m.ctime >= '"+sdateStr+"' ");
		}
		if(StringUtils.isNotEmpty(edateStr)){
			whereSql += (" and m.ctime <= '"+edateStr+"' ");
		}
		if(StringUtils.isNotEmpty(minwords)){
			whereSql += (" and m.word_counts >= "+minwords);
		}
		
		if(StringUtils.isNotEmpty(maxwords)){
			whereSql += (" and m.word_counts <= "+maxwords);
		}
		if(StringUtils.isNotEmpty(qqgroup)){
			whereSql += (" and m.qqgroup = '"+qqgroup+"' ");
		}
		
		if(StringUtils.isNotEmpty(username)){
			whereSql += (" and m.username regexp '"+username+"' ");
		}
		
//		if(StringUtils.isNotEmpty(contentExp)){
//		whereSql += (" and m.content regexp '"+contentExp+"' ");
//	}
	
		//搜索多个词用空格分开
		if (StringUtils.isNotEmpty(content)) {
			String [] words = content.split(" ");
			for(int i = 0; i < words.length; i++ ){
				if(StringUtils.isNotEmpty(words[i])){
					if(StringUtils.isNotEmpty(content)){
						if(isOr){
							if(words.length == 1){
								whereSql += (" and (m.content regexp '"+words[i]+"' )");
							}else if(i == 0){
								whereSql += (" and (m.content regexp '"+words[i]+"' ");
							}else if(i == (words.length -1)){
								whereSql += (" or m.content regexp '"+words[i]+"' )");
							}else{
								whereSql += (" or m.content regexp '"+words[i]+"' ");
							}
							
						}else{
							whereSql += (" and m.content regexp '"+words[i]+"' ");
						}
					}
				}
			}
		}
		
		String postSql = " order by m.qqid,m.ctime desc ";
	
		
		String sql = preSql+whereSql+postSql;
		BigInteger counts = (BigInteger)msgDao.getSession().createSQLQuery("select count(*) from ("+sql+" ) tmp").uniqueResult();
		
		Query query = msgDao.getSession().createSQLQuery(sql);
		
		Iterator iterator =query.list().iterator();
		   while(iterator.hasNext()){
			   Object[] arr = (Object[])iterator.next();	
			   Msg msg = new Msg();
			   msg.setQqid((String)arr[0]);		
			   if(arr[1]!=null){
				   msg.setCtime((Date)arr[1]);
			   }
			   msg.setUsername((String)arr[2]);
			   msg.setQqgroup((String)arr[3]);
			   msg.setContent((String)arr[4]);
			   msg.setWordCounts((Integer)arr[5]);
			   
			   msgList.add(msg);
		   }

		return msgList;
	}



	

}
